package com.huatai.datadevelop.service;

import com.alibaba.cloud.commons.lang.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.huatai.common.error.exception.ServiceException;
import com.huatai.datadevelop.entity.TableManageEntity;
import com.huatai.datadevelop.mapper.TableManageMapper;
import com.huatai.datadevelop.utils.JDBCUtils;
import com.huatai.datadevelop.utils.PageUtils;
import com.huatai.datadevelop.utils.Query;
import com.huatai.datadevelop.vo.TableStructureModel;
import groovy.util.logging.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @Author zhangbenting
 * @Description TODO
 * @Date 2023/9/1 11:41
 * @Version 1.0
 */

@Service
@Slf4j
public class TableManageService extends ServiceImpl<TableManageMapper, TableManageEntity> {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Autowired
	private CommonService commonService;

	/**
	 * 根据表名获取数据表清单
	 * @param tableName
	 * @return
	 */
	public TableManageEntity getEntityByTableName(String tableName){
		QueryWrapper<TableManageEntity> qw = new QueryWrapper<>();
		qw.eq("table_name",tableName);
		TableManageEntity entity = super.getOne(qw);
		return entity;
	}

	/**
	 * 获取数据表是否合规
	 * @param tableName
	 * @return
	 */
	public HashMap<String,Object> getTableNameCompliance(String tableName){
		HashMap<String,Object> hashMap = new HashMap<>();
		hashMap.put("success",true);
		boolean ztb = tableName.startsWith("ztb_");
		if(!ztb){
			hashMap.put("success",false);
			hashMap.put("message","表名称未以ztb_，请调整");
			return hashMap;
		}

		QueryWrapper<TableManageEntity> qw = new QueryWrapper<>();
		qw.eq("table_name",tableName);
		int count = (int) this.count(qw);
		if(count>0){
			hashMap.put("success",false);
			hashMap.put("message","表名称重复，请调整表名称");
			return hashMap;
		}
		hashMap.put("message","验证通过");
		return hashMap;
	}

	/**
	 * 分页获取数据
	 *
	 * @param params
	 * @return
	 */
	public PageUtils queryPage(Map<String, Object> params) {
		String name = (String) params.get("name");
		IPage<TableManageEntity> page = this.page(
			new Query<TableManageEntity>().getPage(params),
			new QueryWrapper<TableManageEntity>().like(StringUtils.isNotBlank(name), "name", name)
		);
		page.getRecords().stream().forEach(p->{
			String tableModel = p.getTableModel();
			List<TableStructureModel> tableStructureModels = JSON.parseArray(tableModel, TableStructureModel.class);
			p.setTableList(tableStructureModels);
			String body = p.getBody();
			if(StringUtils.isNotBlank(body)){
				p.setJsonBody(JSON.parseObject(body));
			}
			String heard = p.getHearder();
			if(StringUtils.isNotBlank(heard)){
				p.setJsonHeard(JSON.parseObject(heard));
			}
		});
		return new PageUtils(page);
	}

	/**
	 * 删除数据
	 * @param ids
	 */
	@Transactional(rollbackFor = Exception.class)
	public void deleteByIds(List<String> ids){
		ids.forEach(id->{
			TableManageEntity byId = this.getById(id);
			String tableName = byId.getTableName();
			//TODO 此处将相关的表删除  表存在关联关系
//			this.exitTableRelation(tableName);
			this.baseMapper.deleteTable(tableName);
			super.removeById(id);
		});
	}

	/**
	 * 新增或者更新数据
	 * @param entity
	 * @return
	 */
	@Transactional(rollbackFor = Exception.class)
	public Boolean saveOrUpdateEntity(TableManageEntity entity) throws SQLException {
		//TODO 查看数据表是否存在，库表里面是否有数据，无数据则删除表格，新建
		Boolean aBoolean = this.exitTable(entity.getTableName());
		if(aBoolean){
			//若无数据，则把数据库表删除
//			this.exitTableRelation(entity.getTableName());
			this.baseMapper.deleteTable(entity.getTableName());
		}

		JSONObject jsonBody = entity.getJsonBody();
		if(jsonBody!=null  && jsonBody.size()>0){
			entity.setBody(JSON.toJSONString(jsonBody));
		}

		JSONObject jsonHeard = entity.getJsonHeard();
		if(jsonHeard!=null && jsonHeard.size()>0){
			entity.setHearder(JSON.toJSONString(jsonHeard));
		}

		//拼接创建sql 语句
		List<TableStructureModel> tableList = entity.getTableList();
		StringBuilder sb = new StringBuilder();
		if(tableList!=null && tableList.size()>0){
			for (int i = 0; i < tableList.size(); i++) {
				TableStructureModel tableStructureModel = tableList.get(i);
				String name = tableStructureModel.getName();
				if(commonService.isNumber(name)){
					throw new ServiceException("库表字段配置第"+(i+1)+"行的列名是数字，暂不支持数字列名");
				}
				if(StringUtils.isNotBlank(name)){
					sb = sb.append(tableStructureModel.getName()).append(" ")
						.append(tableStructureModel.getType()).append("(")
						.append(tableStructureModel.getLength()).append(")")
						.append(" COMMENT '")
						.append(tableStructureModel.getNote())
						.append("', ");
				}
			}
			String collect = tableList.stream().map(p -> p.getName()).collect(Collectors.joining(","));
			entity.setField(collect);
			entity.setTableModel(JSON.toJSONString(entity.getTableList()));
		}
		String sql = " CREATE TABLE " + entity.getTableName();
		if(StringUtils.isNotBlank(sb.toString())){
			sql = sql + " ( auto_id INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT '主键id', " +sb.toString().substring(0,sb.toString().length()-2) + ")";
		}else {
			sql = sql + " ( auto_id INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT '主键id') ";
		}
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = JDBCUtils.getConnection();
			statement = connection.prepareStatement(sql);
			statement.execute();
			return super.saveOrUpdate(entity);
		} catch (Exception e) {
			log.error("新增或更新报错："+e.getMessage());
			connection.rollback();
		}finally {
			JDBCUtils.close(statement,connection);
		}
		return false;
	}


	/**
	 * 查看表是否存在
	 */
	public Boolean exitTable(String tableName){
		String sql = "SHOW TABLES LIKE '" + tableName+"'";
		Boolean query = this.jdbcTemplate.query(sql, null, null, new ResultSetExtractor<Boolean>() {
			@Override
			public Boolean extractData(ResultSet rs) throws SQLException, DataAccessException {
				return rs.next();
			}
		});
		return query;
	}
}
